module.exports = {
  createUser: `INSERT INTO SystemUser (Account, Password, UserName, EMail, Status, CreatedOn)
           VALUES(@account, @password, @userName, @email, @status, @createdOn)`,
  getUser: `select * from SystemUser where Account=@account`,

  recordLogIn: `update SystemUser set LastLogOn=@lastLogOn where Id=@id`,

  changeStatus: `update SystemUser set Status=@status where Id=@id`,

  changePassword: `update SystemUser set Password=@password where Id=@id`,

  updateUserInfo: `update SystemUser set UserName=@userName, EMail=@email where Id=@id`,

  userSearch: `select * from SystemUser`,

  userSearchExceptRole: `with cte as (
    select * from SystemUser 
    except
    select a.* from SystemUser a
    inner join SystemUserRole b on b.UserId=a.Id
    where b.RoleId=@roleId
  )
  select * from cte`,

  userSearchExceptRoleCount: `with cte as (
    select * from SystemUser 
    except
    select a.* from SystemUser a
    inner join SystemUserRole b on b.UserId=a.Id
    where b.RoleId=@roleId
  )
  select count(*) as totals from cte`,

  checkUserCanRemove: `select
    case when c.Name='超级管理员' then 1 else 0 end Enabled
  from SystemUser a
  left join SystemUserRole b on a.Id = b.UserId 
  left join SystemRole c on c.Id = b.RoleId
  WHERE a.Id=@userId`,

  removeUser: `DELETE from SystemUser where Id = @userId;
    DELETE from SystemUserRole where UserId = @userId;
    DELETE from SystemUserMenu where UserId = @userId`,

  createRole: `INSERT INTO SystemRole (Name, CreatedOn) VALUES(@name, @createdOn)`,

  getRole: `select * from SystemRole where Name=@name`,

  getRoleById: `select * from SystemRole where Id=@id`,

  checkRoleName: `select * from SystemRole where Id <> @id and Name = @name`,

  updateRole: `UPDATE SystemRole SET Name=@name WHERE Id=@id`,

  roleSearch: `select * from SystemRole`,

  searchRoleUsers: `SELECT a.* FROM SystemUser a
    INNER JOIN SystemUserRole b ON b.UserId = a.Id`,

  addRoleUser: `INSERT INTO SystemUserRole (RoleId, UserId, CreatedOn) VALUES(@roleId, @userId, datetime('now'))`,

  removeRoleUser: `DELETE FROM SystemUserRole WHERE RoleId=@roleId AND UserId=@userId`,

  removeRole: `DELETE from SystemRole where Id = @roleId;
    DELETE from SystemUserRole where RoleId = @roleId;
    DELETE from SystemRoleMenu where RoleId = @roleId`
};